﻿Imports System.Text
Public Class SQLScripts

#Region "Classes"
    Public Class Table
        Public Const cTABLE_NAME As String = "TABLE_NAME"
        Public Const cTABLE_NAME_Column_ID As Integer = 0
    End Class

    Public Class Fields
        Public Const cCOLUMN_NAME As String = "COLUMN_NAME"
        Public Const cCOLUMN_NAME_Column_ID As Integer = 0

        Public Const cIS_NULLABLE As String = "IS_NULLABLE"
        Public Const cIS_NULLABLE_Column_ID As Integer = 1

        Public Const cDATA_TYPE As String = "DATA_TYPE"
        Public Const cDATA_TYPE_Column_ID As Integer = 2

        Public Const cCHARACTER_MAXIMUM_LENGTH As String = "CHARACTER_MAXIMUM_LENGTH"
        Public Const cCHARACTER_MAXIMUM_LENGTH_Column_ID As Integer = 3

        Public Const cNUMERIC_PRECISION As String = "NUMERIC_PRECISION"
        Public Const cNUMERIC_PRECISION_Column_ID As Integer = 4

        Public Const cCHARACTER_SET_NAME As String = "CHARACTER_SET_NAME"
        Public Const cCHARACTER_SET_NAME_Column_ID As Integer = 5

        Public Const cCOLLATION_NAME As String = "COLLATION_NAME"
        Public Const cCOLLATION_NAME_Column_ID As Integer = 6

        Public Const cORDINAL_POSITION As String = "ORDINAL_POSITION"
        Public Const cORDINAL_POSITION_Column_ID As Integer = 7

        Public Const cTABLE_NAME As String = "TABLE_NAME"
        Public Const cTABLE_NAME_Column_ID As Integer = 8
    End Class

    Public Class ForeignKey
        Public Const cForeignKey_TableName As String = "ForeignKey_TableName"
        Public Const cForeignKey_TableName_Column_ID As Integer = 0

        Public Const cForeignKey_ColumnName As String = "ForeignKey_ColumnName"
        Public Const cForeignKey_ColumnName_Column_ID As Integer = 1

        Public Const cPrimaryKey_TableName As String = "PrimaryKey_TableName"
        Public Const cPrimaryKey_TableName_Column_ID As Integer = 2
    End Class

    Public Class Views
        Public Const cViewName As String = ""
        Public Const cViewName_Column_ID As Integer = 0
    End Class
#End Region

    Public Shared ReadOnly Property GetAllViews() As String
        Get
            Dim mySQL As New StringBuilder
            With mySQL
                .Append(String.Format("SELECT {0} FROM INFORMATION_SCHEMA.VIEWS ", Table.cTABLE_NAME))
                .Append(String.Format("ORDER BY {0}", Table.cTABLE_NAME))
            End With
            Return mySQL.ToString
        End Get
    End Property

    Public Shared ReadOnly Property GetAllTables() As String
        Get
            Dim mySQL As New StringBuilder
            With mySQL
                .Append(String.Format("SELECT {0} FROM INFORMATION_SCHEMA.TABLES ", Table.cTABLE_NAME))
                .Append("WHERE TABLE_TYPE='BASE TABLE' ")
                .Append(String.Format("ORDER BY {0}", Table.cTABLE_NAME))
            End With
            Return mySQL.ToString
        End Get
    End Property

    Public Shared ReadOnly Property GetAllFields() As String
        Get
            Dim mySQL As New StringBuilder
            With mySQL
                .Append("SELECT ")
                .Append(String.Format("{0},", Fields.cCOLUMN_NAME))
                .Append(String.Format("{0},", Fields.cIS_NULLABLE))
                .Append(String.Format("{0},", Fields.cDATA_TYPE))
                .Append(String.Format("{0},", Fields.cCHARACTER_MAXIMUM_LENGTH))
                .Append(String.Format("{0},", Fields.cNUMERIC_PRECISION))
                .Append(String.Format("{0},", Fields.cCHARACTER_SET_NAME))
                .Append(String.Format("{0},", Fields.cCOLLATION_NAME))
                .Append(String.Format("{0},", Fields.cORDINAL_POSITION))
                .Append(String.Format("{0} ", Fields.cTABLE_NAME))
                .Append("FROM INFORMATION_SCHEMA.COLUMNS ")
                .Append(String.Format("ORDER BY {0}, {1}", Fields.cTABLE_NAME, Fields.cORDINAL_POSITION))
            End With
            Return mySQL.ToString
        End Get
    End Property

    Public Shared ReadOnly Property GetAllForeignKeys() As String
        Get
            Dim mySQL As New StringBuilder
            With mySQL
                .Append(String.Format("SELECT INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME AS {0}, ", ForeignKey.cForeignKey_TableName))
                .Append(String.Format("INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME AS {0}, ", ForeignKey.cForeignKey_ColumnName))
                .Append(String.Format("INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME AS {0} ", ForeignKey.cPrimaryKey_TableName))
                .Append("FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME ")
                .Append(String.Format("ORDER BY {0},{1}", ForeignKey.cForeignKey_TableName, ForeignKey.cPrimaryKey_TableName))
            End With
            Return mySQL.ToString
        End Get
    End Property

    Public Shared Function GetWriteView(ByVal aTableInfos As Dictionary(Of String, TableInfo), ByVal aTableInfo As TableInfo) As String
        Dim myFields As New List(Of String)
        Dim myViewName As String = aTableInfo.ViewName
        Dim mySQL As New StringBuilder
        With mySQL
            .AppendLine(String.Format("CREATE VIEW [{0}]", myViewName))
            .AppendLine("AS")
            .Append("SELECT ")
            Dim myTableName As String = aTableInfo.TableName
            Dim myIsFirst As Boolean = True
            Dim myInnerJoin As New List(Of String)
            For Each myFieldInfo As FieldInfo In aTableInfo.FieldInfos.Values
                Dim myFieldName As String = myFieldInfo.FieldName
                If (Not myFields.Contains(myFieldName.ToLowerInvariant)) Then
                    myFields.Add(myFieldName.ToLowerInvariant)
                    If myIsFirst Then
                        myIsFirst = False
                    Else
                        .Append(", ")
                    End If
                    .AppendLine(String.Format("[{0}].[{1}]", myTableName, myFieldInfo.FieldName))
                End If
                For Each myForeignKeyInfo As ForeignKeyInfo In myFieldInfo.ForeignKeyInfos.Values
                    Dim myColumn As String = myForeignKeyInfo.PrimaryKey_ColumnName
                    Dim myPrimaryTable As String = myForeignKeyInfo.PrimaryKey_TableName
                    'INNER JOIN [Codepage] ON Attribute.Codepage_ID = [Codepage].CodePage_ID
                    Dim myJoin As String = String.Format(" INNER JOIN [{0}] ON [{1}].[{2}] = [{0}].[{2}]", myPrimaryTable, myTableName, myColumn)
                    If Not myInnerJoin.Contains(myJoin) Then myInnerJoin.Add(myJoin)
                    If (Not myFields.Contains(myColumn.ToLowerInvariant)) Then
                        myFields.Add(myColumn.ToLowerInvariant)
                        If myIsFirst Then
                            myIsFirst = False
                        Else
                            .Append(", ")
                        End If
                        .AppendLine(String.Format("[{0}].[{1}]", myPrimaryTable, myColumn))
                    End If
                    'get the primary table infos
                    Dim myTableInfoPrimaryTable As TableInfo = aTableInfos.Item(myPrimaryTable.ToLowerInvariant)
                    If Not myTableInfoPrimaryTable Is Nothing Then
                        'test
                        For Each myFieldInfoPrimary As FieldInfo In myTableInfoPrimaryTable.FieldInfos.Values
                            Dim myFieldNameToCheck As String = myFieldInfoPrimary.FieldName
                            If (Not myFields.Contains(myFieldNameToCheck.ToLowerInvariant)) Then
                                myFields.Add(myFieldNameToCheck.ToLowerInvariant)
                                If myIsFirst Then
                                    myIsFirst = False
                                Else
                                    .Append(", ")
                                End If
                                .AppendLine(String.Format("[{0}].[{1}]", myPrimaryTable, myFieldNameToCheck))
                            End If
                        Next
                    End If
                Next
            Next

            .AppendLine(String.Format(" FROM [{0}]", myTableName))
            For Each myJoin As String In myInnerJoin
                .AppendLine(myJoin)
            Next
        End With
        Return mySQL.ToString
    End Function

End Class
